/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.sun.courier.dal;

import com.sun.courier.entity.Department;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author HONG QUAN
 */
public class DepartmentDAL {
    DataAccessHelper dah = new DataAccessHelper();
    public ArrayList<Department> showAllStore(){
        String strQuery = "Select DepId, DepartmentName from Department where Department.Role = '3'";
        ArrayList<Department> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Department d = new Department();
                d.setDepId(rs.getInt("DepId"));
                d.setDepartmentName(rs.getString("DepartmentName"));
                lst.add(d);
            }
        } catch (Exception e) {
        }
        return lst;
    }
    
    public ArrayList<Department> showAllDespatching(){
        String strQuery = "Select DepId, DepartmentName from Department where Department.Role = '2'";// DA BO  or Department.Role ='1'
        ArrayList<Department> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Department d = new Department();
                d.setDepId(rs.getInt("DepId"));
                d.setDepartmentName(rs.getString("DepartmentName"));
                lst.add(d);
            }
        } catch (Exception e) {
        }
        return lst;
    }
    
    public ArrayList<Department> showAllReceive_Return(){
        String strQuery = "Select DepId, DepartmentName from Department where Department.Role = '1'";// DA BO  or Department.Role ='1'
        ArrayList<Department> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Department d = new Department();
                d.setDepId(rs.getInt("DepId"));
                d.setDepartmentName(rs.getString("DepartmentName"));
                lst.add(d);
            }
        } catch (Exception e) {
        }
        return lst;
    }
    
    public int getDepartmentRoleByEmpId(int empId){
        String strQuery = "Select Role from Department join Employee on Department.DepId = Employee.DepId where Employee.EmpId = '"+empId+"'";// DA BO  or Department.Role ='1'
        int role = -1;
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                role = rs.getInt("Role");
            }
        } catch (Exception e) {
        }
        return role;
    }
    
    /////////////////////////////////////////////////////NAMBH/////////////////////////////////////////
     private final static String INSERT = "insert into Department(DepartmentName, Description, Role) values(?, ?, ?)";
    private final static String UPDATE = "update Department set DepartmentName=?, Description=?, Role=? where DepId=?";
    private final static String DELETE = "delete Department where DepId=?";
    private final static String GET_ALL = "select * from Department ORDER BY DepId desc";
    private final static String GET_BY_ID = "select * from Department where DepId=?";
    private final static String GET_AT_ROW = "WITH DepartmentTmp AS ( "
            + "SELECT ROW_NUMBER() OVER (ORDER BY DepId desc) AS rownum, * FROM Department) "
            + "SELECT * FROM DepartmentTmp WHERE rownum = ?";
    ;
    
    
    Connection con;


    public boolean add(Department obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(INSERT);
                    pre.setString(1, obj.getDepartmentName());
                    pre.setString(2, obj.getDescription());
                    pre.setInt(3, obj.getRole());
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean edit(Department obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(UPDATE);
                    pre.setString(1, obj.getDepartmentName());
                    pre.setString(2, obj.getDescription());
                    pre.setInt(3, obj.getRole());
                    pre.setInt(4, obj.getDepId());
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean delete(int depId) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(DELETE);
                    pre.setInt(1, depId);
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public ArrayList<Department> getAll() {
        ArrayList<Department> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                ResultSet rs = con.prepareStatement(GET_ALL).executeQuery();
                try {
                    while (rs.next()) {
                        Department obj = new Department();
                        obj.setDepId(rs.getInt("DepId"));
                        obj.setDepartmentName(rs.getString("DepartmentName"));
                        obj.setDescription(rs.getString("Description"));
                        obj.setRole(rs.getInt("Role"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }
    
    public Department getById(int depId) {
        Department obj = null;
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_BY_ID);
                    pre.setInt(1, depId);
                    ResultSet rs = pre.executeQuery();
                    if (rs.next()) {
                        obj = new Department();
                        obj.setDepId(rs.getInt("DepId"));
                        obj.setDepartmentName(rs.getString("DepartmentName"));
                        obj.setDescription(rs.getString("Description"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return obj;
    }

    public Department getAtRow(int rowNum) {
        Department obj = null;
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_AT_ROW);
                    pre.setInt(1, rowNum);
                    ResultSet rs = pre.executeQuery();
                    if (rs.next()) {
                        obj = new Department();
                        obj.setDepId(rs.getInt("DepId"));
                        obj.setDepartmentName(rs.getString("DepartmentName"));
                        obj.setDescription(rs.getString("Description"));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return obj;
    }

}
